﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Text;

/// <summary>
///BQQL 的摘要说明
/// </summary>
public class BQQL
{
  public BQQL()
  {
    //
    //TODO: 在此处添加构造函数逻辑
    //
  }
  public static DataSet Get_BQQL(string cbsjid, string yqxzid)
  {
    string sql = "select * from sjlr_bqql where cbsjid=@0 and yqxzid=@1";
    SqlParameter[] cmdParam = new SqlParameter[] { new SqlParameter("@0", SqlDbType.VarChar, 20), 
      new SqlParameter("@1", SqlDbType.VarChar, 10) };
    cmdParam[0].Value = cbsjid;
    cmdParam[1].Value = yqxzid;
    DataSet ds = DbHelperSQL.Query(sql, cmdParam);
    return ds;
  }
  /// <summary>
  /// 增加一条数据
  /// </summary>
  public static CommandInfo Add(BQQL_Model model)
  {
    StringBuilder strSql = new StringBuilder();
    strSql.Append("insert into SJLR_BQQL(");
    strSql.Append("YQXZID,CBSJID,BQQL,BQQL_SFSB,JG,JJ,YSJG,YSJJ,YSFY,SSQL,SSJG,SSJJ,SSFY,shr,shsj)");
    strSql.Append(" values (");
    strSql.Append("@YQXZID,@CBSJID,@BQQL,@BQQL_SFSB,@JG,@JJ,@YSJG,@YSJJ,@YSFY,@SSQL,@SSJG,@SSJJ,@SSFY,@shr,@shsj)");
    SqlParameter[] parameters = {
					new SqlParameter("@YQXZID", SqlDbType.Int,4),
					new SqlParameter("@CBSJID", SqlDbType.Int,4),
					new SqlParameter("@BQQL", SqlDbType.BigInt,8),
					new SqlParameter("@BQQL_SFSB", SqlDbType.Char,1),
					new SqlParameter("@JG", SqlDbType.Decimal,9),
					new SqlParameter("@JJ", SqlDbType.Decimal,9),
					new SqlParameter("@YSJG", SqlDbType.Decimal,9),
					new SqlParameter("@YSJJ", SqlDbType.Decimal,9),
					new SqlParameter("@YSFY", SqlDbType.Decimal,9),
					new SqlParameter("@SSQL", SqlDbType.Decimal,9),
					new SqlParameter("@SSJG", SqlDbType.Decimal,9),
					new SqlParameter("@SSJJ", SqlDbType.Decimal,9),
					new SqlParameter("@SSFY", SqlDbType.Decimal,9),
					new SqlParameter("@shr", SqlDbType.VarChar,20),
					new SqlParameter("@shsj", SqlDbType.DateTime)};
    parameters[0].Value = model.YQXZID;
    parameters[1].Value = model.CBSJID;
    parameters[2].Value = model.BQQL;
    parameters[3].Value = model.BQQL_SFSB;
    parameters[4].Value = model.JG;
    parameters[5].Value = model.JJ;
    parameters[6].Value = model.YSJG;
    parameters[7].Value = model.YSJJ;
    parameters[8].Value = model.YSFY;
    parameters[9].Value = model.SSQL;
    parameters[10].Value = model.SSJG;
    parameters[11].Value = model.SSJJ;
    parameters[12].Value = model.SSFY;
    parameters[13].Value = model.shr;
    parameters[14].Value = model.shsj;
    CommandInfo ci = new CommandInfo();
    ci.CommandText = strSql.ToString();
    ci.Parameters = parameters;
    return ci;
  }
  /// <summary>
  /// 更新一条数据
  /// </summary>
  public static CommandInfo Update(BQQL_Model model)
  {
    StringBuilder strSql = new StringBuilder();
    strSql.Append("update SJLR_BQQL set ");
    strSql.Append("BQQL=@BQQL,");
    strSql.Append("BQQL_SFSB=@BQQL_SFSB,");
    strSql.Append("JG=@JG,");
    strSql.Append("JJ=@JJ,");
    strSql.Append("YSJG=@YSJG,");
    strSql.Append("YSJJ=@YSJJ,");
    strSql.Append("YSFY=@YSFY,");
    strSql.Append("SSQL=@SSQL,");
    strSql.Append("SSJG=@SSJG,");
    strSql.Append("SSJJ=@SSJJ,");
    strSql.Append("SSFY=@SSFY");
    strSql.Append(" where YQXZID=@YQXZID and CBSJID=@CBSJID ");
    SqlParameter[] parameters = {
					new SqlParameter("@BQQL", SqlDbType.BigInt,8),
					new SqlParameter("@BQQL_SFSB", SqlDbType.Char,1),
					new SqlParameter("@JG", SqlDbType.Decimal,9),
					new SqlParameter("@JJ", SqlDbType.Decimal,9),
					new SqlParameter("@YSJG", SqlDbType.Decimal,9),
					new SqlParameter("@YSJJ", SqlDbType.Decimal,9),
					new SqlParameter("@YSFY", SqlDbType.Decimal,9),
					new SqlParameter("@SSQL", SqlDbType.Decimal,9),
					new SqlParameter("@SSJG", SqlDbType.Decimal,9),
					new SqlParameter("@SSJJ", SqlDbType.Decimal,9),
					new SqlParameter("@SSFY", SqlDbType.Decimal,9),
					new SqlParameter("@YQXZID", SqlDbType.Int,4),
					new SqlParameter("@CBSJID", SqlDbType.Int,4)};
    parameters[0].Value = model.BQQL;
    parameters[1].Value = model.BQQL_SFSB;
    parameters[2].Value = model.JG;
    parameters[3].Value = model.JJ;
    parameters[4].Value = model.YSJG;
    parameters[5].Value = model.YSJJ;
    parameters[6].Value = model.YSFY;
    parameters[7].Value = model.SSQL;
    parameters[8].Value = model.SSJG;
    parameters[9].Value = model.SSJJ;
    parameters[10].Value = model.SSFY;
    parameters[11].Value = model.YQXZID;
    parameters[12].Value = model.CBSJID;
    CommandInfo ci = new CommandInfo();
    ci.CommandText = strSql.ToString();
    ci.Parameters = parameters;
    return ci;
  }
  /// <summary>
  /// 得到一个对象实体
  /// </summary>
  public static BQQL_Model GetModel(int YQXZID, int CBSJID)
  {

    StringBuilder strSql = new StringBuilder();
    strSql.Append("select  top 1 YQXZID,CBSJID,BQQL,BQQL_SFSB,JG,JJ,YSJG,YSJJ,YSFY,SSQL,SSJG,SSJJ,SSFY from SJLR_BQQL ");
    strSql.Append(" where YQXZID=@YQXZID and CBSJID=@CBSJID ");
    SqlParameter[] parameters = {
					new SqlParameter("@YQXZID", SqlDbType.Int,4),
					new SqlParameter("@CBSJID", SqlDbType.Int,4)			};
    parameters[0].Value = YQXZID;
    parameters[1].Value = CBSJID;

    BQQL_Model model = new BQQL_Model();
    DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
    if (ds.Tables[0].Rows.Count > 0)
    {
      return DataRowToModel(ds.Tables[0].Rows[0]);
    }
    else
    {
      return null;
    }
  }
  /// <summary>
  /// 得到一个对象实体
  /// </summary>
  public static BQQL_Model DataRowToModel(DataRow row)
  {
    BQQL_Model model = new BQQL_Model();
    if (row != null)
    {
      if (row["YQXZID"] != null && row["YQXZID"].ToString() != "")
      {
        model.YQXZID = int.Parse(row["YQXZID"].ToString());
      }
      if (row["CBSJID"] != null && row["CBSJID"].ToString() != "")
      {
        model.CBSJID = int.Parse(row["CBSJID"].ToString());
      }
      if (row["BQQL"] != null && row["BQQL"].ToString() != "")
      {
        model.BQQL = long.Parse(row["BQQL"].ToString());
      }
      if (row["BQQL_SFSB"] != null)
      {
        model.BQQL_SFSB = row["BQQL_SFSB"].ToString();
      }
      if (row["JG"] != null && row["JG"].ToString() != "")
      {
        model.JG = decimal.Parse(row["JG"].ToString());
      }
      if (row["JJ"] != null && row["JJ"].ToString() != "")
      {
        model.JJ = decimal.Parse(row["JJ"].ToString());
      }
      if (row["YSJG"] != null && row["YSJG"].ToString() != "")
      {
        model.YSJG = decimal.Parse(row["YSJG"].ToString());
      }
      if (row["YSJJ"] != null && row["YSJJ"].ToString() != "")
      {
        model.YSJJ = decimal.Parse(row["YSJJ"].ToString());
      }
      if (row["YSFY"] != null && row["YSFY"].ToString() != "")
      {
        model.YSFY = decimal.Parse(row["YSFY"].ToString());
      }
      if (row["SSQL"] != null && row["SSQL"].ToString() != "")
      {
        model.SSQL = decimal.Parse(row["SSQL"].ToString());
      }
      if (row["SSJG"] != null && row["SSJG"].ToString() != "")
      {
        model.SSJG = decimal.Parse(row["SSJG"].ToString());
      }
      if (row["SSJJ"] != null && row["SSJJ"].ToString() != "")
      {
        model.SSJJ = decimal.Parse(row["SSJJ"].ToString());
      }
      if (row["SSFY"] != null && row["SSFY"].ToString() != "")
      {
        model.SSFY = decimal.Parse(row["SSFY"].ToString());
      }
    }
    return model;
  }
  public static CommandInfo Delete(string cbsjid)
  {
    string sql = "delete from sjlr_bqql where cbsjid=@0";
    SqlParameter[] cmdParams = new SqlParameter[] { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = cbsjid;
    CommandInfo ci = new CommandInfo();
    ci.CommandText = sql;
    ci.Parameters = cmdParams;
    return ci;
  }
  public static DataSet Get_BQQL_YQXZ(string cbsjid)
  {
    string sSql = @"select a.* from v_bqql a,
(select top 1 * from SJLR_JGTZSJ order by ID desc) d
where a.cbsjid=@0 and charindex(a.YQXZ,d.YQXZ)>0";
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
    cmdParams[0].Value = cbsjid;
    DataSet ds = DbHelperSQL.Query(sSql, cmdParams);
    return ds;
  }
}